using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using SpreadsheetGear;
using Sustainalytics.ClientDW.DataAccessLayer;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.Entities.ESGRatings;
using Sustainalytics.EsgRatings.BusinessLogic;
using Sustainalytics.ESG.Entities;
using Sustainalytics.ESGRatings.BusinessLogic.Models;
using Sustainalytics.Utils;
using KeyEsgIssueIndicator = Sustainalytics.ESG.Entities.KeyEsgIssueIndicator;
using KeyEsgIssueIndicatorType = Sustainalytics.ESG.Entities.KeyEsgIssueIndicatorType;

namespace Sustainalytics.ESGRatings.BusinessLogic
{
    using Sustainalytics.DataLayer.EsgRatings;

    public enum ProcessKeyESGIssueTemplateResult
    {
        ProcessingSucceded,
        ValidationFailed,
        NoFilesToProcess,
    }

    public class KeyEsgIssuesManagement : IKeyESGIssuesManagement
    {
        private readonly IClientDwRepository _clientDwRepository;
        private readonly IEsgInputFileRepository _esgInputFileRepository;
        private readonly IKeyEsgIssuesRepository _keyEsgIssuesRepository;
        private const int StartingRowNumber = 2;
        private const int SubIndustryCodeCell = 1;
        private const int KeyEsgCodeCell = 3;
        private const int RpIndicatorRow = 0;
        private const int EventIndicatorRow = 1;



        private const int TypeAIndicatorSheet = 0;
        private const int TypeBIndicatorSheet = 1;
        private const int ValuesSheet = 2;
        private const int EventIndicatorsSheet = 3;

        private Dictionary<int,string> _sheetDictionary=new Dictionary<int, string>()
        {
            {TypeAIndicatorSheet,"Indicators A"},
            {TypeBIndicatorSheet,"Indicators B"},
            {ValuesSheet,"Values"},
            {EventIndicatorsSheet,"Events"}
        }; //TODO it would be nice to move this declaration in a app.config

        private FileUploadLogs _validationErrors;

        private readonly List<string> _logMessages; 

        private List<KeyEsgIssueImpact> _keyEsgIssues;




        private KeyEsgIssuesManagement(IClientDwRepository clientDwRepository,
                                       IEsgInputFileRepository esgInputFileRepository,
                                       IKeyEsgIssuesRepository keyEsgIssuesRepository):this()
        {
           

            _clientDwRepository = clientDwRepository;
            _esgInputFileRepository = esgInputFileRepository;
            _keyEsgIssuesRepository = keyEsgIssuesRepository;

        }
        public KeyEsgIssuesManagement()
        {
            _logMessages = new List<string>();
            _keyEsgIssues = new List<KeyEsgIssueImpact>();
        }

        public static IKeyESGIssuesManagement GetInstance(IClientDwRepository clientDwRepository,
                                                          IEsgInputFileRepository esgInputFileRepository,
                                                          IKeyEsgIssuesRepository keyEsgIssuesRepository)
        {
            if (clientDwRepository == null)
            {
                throw new ArgumentException("No instance of IClientDwRepository provided for creating an IKeyESGIssuesManagement!");
            }

            if (esgInputFileRepository == null)
            {
                throw new ArgumentException("No instance of IEsgInputFileRepository provided for creating an IKeyESGIssuesManagement!");
            }
            
            return new KeyEsgIssuesManagement(clientDwRepository, esgInputFileRepository, keyEsgIssuesRepository);
        }

        public FileUploadLogs LoadKeyESGIssuesForProcessing(string keyEsgFileName, byte[] keyEsgFileContent, string keyEsgFileContentType)
        {
            var keyEsgFileContentBytes = keyEsgFileContent == null ? -1 : keyEsgFileContent.Length;

            try
            {
                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingStart(keyEsgFileName, keyEsgFileContentBytes, keyEsgFileContentType);

                if (keyEsgFileContentBytes <= 0)
                {
                    throw new ArgumentException("No file uploaded for key esg issues processing (i.e. null or empty byte array)!");
                }

                var keyEsgIssuesWorkbook = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(keyEsgFileContent);

                keyEsgIssuesWorkbook.FullName = keyEsgFileName;

                EnsureTemplateConsistency(keyEsgFileContent);

                if (_validationErrors.Errors == string.Empty)
                    _esgInputFileRepository.AddFile(new EsgInputFileComplete
                    {
                        Id = Guid.NewGuid(),
                        Name = "Key ESG Issues",
                        Type = EsgInputFileType.KeyESGIssues,
                        FileName = keyEsgFileName,
                        Content = keyEsgFileContent,
                        ContentType = keyEsgFileContentType
                    });

                var fileUploadLogs = new FileUploadLogs { Errors = _validationErrors.Errors };

                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingSucceeded(keyEsgFileName, keyEsgFileContentBytes, keyEsgFileContentType, fileUploadLogs.Errors);

                return fileUploadLogs;
            }
            catch (Exception e)
            {
                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingFailed(keyEsgFileName, keyEsgFileContentBytes, keyEsgFileContentType, e.GetType().ToString(), e.Message, ExceptionUtility.GetInfo(e));

                throw;
            }
            finally
            {
                WeightMatricesEventSource.Log.LoadWeightMatrixForProcessingStop(keyEsgFileName, keyEsgFileContentBytes, keyEsgFileContentType);
            }
        }

        private void EnsureTemplateConsistency(byte[] data)
        {
            _logMessages.Clear();
            _validationErrors = new FileUploadLogs();

            var book = Factory.GetWorkbookSet().Workbooks.OpenFromMemory(data);

            var outputValidations=DoValidations(book, false);
            if (outputValidations.Item1) return;
            if (!outputValidations.Item2.Any()) return;
            var agg =
                outputValidations.Item2.Aggregate(
                    (a, b) => string.Format("{0}{1}{2}", a, Environment.NewLine, b));
            _validationErrors.Errors = agg;
        }

        private Tuple<bool,List<string>> DoValidations(IWorkbook book, bool process)
        {
            var output = CheckWorkbookSheetsTemplate(book);
            if (!output.Item1)
            {
                return new Tuple<bool, List<string>>(output.Item1,new List<string>(){output.Item2});
            }
            var listOutput = new List<string>();
            var outputIndicators=ValidateIndicatorsSheet(book,process);
            if (!outputIndicators.Item1)
            {
                listOutput.AddRange(outputIndicators.Item2);
            }

            var sheetOutput=ValidateValuesSheet(book, process);
            if (!sheetOutput.Item1)
            {
                sheetOutput.Item2.Insert(0,"There were some errors on the values sheet");
                listOutput.AddRange(sheetOutput.Item2);
            }

            var eventIndicatorSheet=ValidateEventIndicatorsSheet(book, process);
            if (!eventIndicatorSheet.Item1)
            {
                eventIndicatorSheet.Item2.Insert(0,"There were some errors on the event indicator sheet");
                listOutput.AddRange(eventIndicatorSheet.Item2);
            }
            return new Tuple<bool, List<string>>(!listOutput.Any(),listOutput);
        }

        private Tuple<bool,string> CheckWorkbookSheetsTemplate(IWorkbook book)
        {
            if (book.Sheets.Count == 0) { return new Tuple<bool, string>(false,"There are no sheets to be processed.");}
            var cast =
                book.Sheets.Cast<ISheet>().Select(a => a.Name).Aggregate((a, b) => string.Format("{0},{1}", a, b));
            var template = _sheetDictionary.Select(a => a.Value).Aggregate((a, b) => string.Format("{0},{1}", a, b));
            var equal = template.Equals(cast);
            return new Tuple<bool, string>(equal,
                (equal)
                    ? string.Empty
                    : string.Format(
                        "The excel file sent for processing does not have the expected sheets name format:{0}.The file uploaded has the following format:{1}",
                        template, cast));
        }

        #region ValidatorsAndUpdaters
        private Tuple<bool, List<string>> ValidateIndicatorsSheet(IWorkbook book, bool process)
        {
            var output1=ValidateIndicatorType(book, process, TypeAIndicatorSheet);
            if (!output1.Item1)
            {
                output1.Item2.Insert(0,"There were errors on the indicator sheet Type A");
            }
            var output2 = ValidateIndicatorType(book, process, TypeBIndicatorSheet);
            if (!output2.Item1)
            {
                output2.Item2.Insert(0, "There were errors on the indicator sheet Type B");
            }
            output1.Item2.AddRange(output2.Item2);

            return new Tuple<bool, List<string>>(output1.Item1 && output2.Item1, output1.Item2);
        }

        private Tuple<bool, List<string>> ValidateIndicatorType(IWorkbook book, bool process, int typeIndicatorSheet)
        {
            var errors = new List<string>();

            var indicatorsSheet = book.Worksheets[typeIndicatorSheet];
            int rowNumber = StartingRowNumber;

            int numberOfRpIndicators = GetNumberOfRpIndicators(book, typeIndicatorSheet);

            var isValid = true;
            List<IndicatorTemplate> indicatorTemplate = _clientDwRepository.ListIndicatorTemplatesIdAndNumber().ToList();

            for (; ; )
            {
                if ((indicatorsSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) &&
                    (indicatorsSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                    break;

                if ((indicatorsSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) ||
                    (indicatorsSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                {
                    isValid = false;
                    if (indicatorsSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Sub Industry Code on row: {0} can not be empty", (rowNumber + 1)));
                    }
                    if (indicatorsSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Key ESG Issue Code on row: {0} can not be empty", (rowNumber + 1)));
                    }
                    
                }
                else
                {
                    var tempEsgIssue = new KeyEsgIssueImpact { TemporaryIndicators = new List<KeyEsgIssueIndicator>(), CompanyTypeTemplate = GetCompanyTemplateType(typeIndicatorSheet) };
                    for (var j = 0; j <= numberOfRpIndicators; j++)
                    {
                        if (indicatorsSheet.Cells[rowNumber, j + 5].Value != null)
                            if ((indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "1") ||
                                (indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "-1"))
                            {
                                KeyEsgIssueIndicator indicator = new KeyEsgIssueIndicator
                                {
                                    Name =
                                        indicatorTemplate.Where(
                                            it =>
                                                it.Number ==
                                                indicatorsSheet.Cells[RpIndicatorRow, j + 5].Value.ToString())
                                            .Select(it => it.Name)
                                            .SingleOrDefault(),
                                    Number =
                                        indicatorTemplate.Where(
                                            it =>
                                                it.Number ==
                                                indicatorsSheet.Cells[RpIndicatorRow, j + 5].Value.ToString())
                                            .Select(it => it.Number)
                                            .SingleOrDefault(),
                                    Type = KeyEsgIssueIndicatorType.StandardIndicator,
                                    
                                };


                                if ((indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "1"))
                                {
                                    indicator.HidenToClient = false;
                                }
                                else if (indicatorsSheet.Cells[rowNumber, j + 5].Value.ToString() == "-1")
                                {
                                    indicator.HidenToClient = true;
                                }

                                //add the 4 scores

                                tempEsgIssue.TemporaryIndicators.Add(indicator);
                            }
                    }


                    if (process)
                    {
                        tempEsgIssue.Id = Guid.NewGuid();
                        tempEsgIssue.SubPeerGroupId =
                            int.Parse(indicatorsSheet.Cells[rowNumber, SubIndustryCodeCell].Value.ToString());
                        tempEsgIssue.KeyEsgIssueCode =
                            int.Parse(indicatorsSheet.Cells[rowNumber, KeyEsgCodeCell].Value.ToString());

                        _keyEsgIssues.Add(tempEsgIssue);
                    }

                    
                }
                rowNumber++;
            }
            return new Tuple<bool, List<string>>(isValid,errors);
        }

        private string GetCompanyTemplateType(int typeIndicatorSheet)
        {
            switch (typeIndicatorSheet)
            {
                case TypeAIndicatorSheet:
                    return "Type A";
                case TypeBIndicatorSheet:
                    return "Type B";
            }
            return string.Empty;
        }

        private Tuple<bool,List<string>> ValidateValuesSheet(IWorkbook book, bool process)
        {
            var errors = new List<string>();
            var valuesSheet = book.Worksheets[ValuesSheet];

            var rowNumber = StartingRowNumber;

            for (int i = 5; ; )
            {
                if ((valuesSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) && (valuesSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                    break;

                if ((valuesSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) || (valuesSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                {
                    if (valuesSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Sub Industry Code on row:{0} cannot be empty", (rowNumber + 1)));
                    }

                    if (valuesSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Key ESG Issue Code on row:{0} cannot be empty", (rowNumber + 1)));
                    }
                }
                else
                {
                    var tempEsgIssue = new KeyEsgIssueImpact();

                    tempEsgIssue.Id = Guid.NewGuid();

                    if (valuesSheet.Cells[rowNumber, i].Value != null)
                        if ((int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString()) >= 0) && (int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString()) <= 10))
                        {
                            tempEsgIssue.SustainabilityImpact = int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString());
                        }
                        else
                        {
                            errors.Add(string.Format("Sustainability Impact values have to be between 0 and 10. Row: {0}", (rowNumber + 1)));
                        }
                    else
                    {
                        errors.Add(string.Format("Sustainability Impact cell on row: {0} is empty", (rowNumber + 1)));
                    }

                    if (valuesSheet.Cells[rowNumber, i + 1].Value != null)
                        if ((int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString()) >= 0) && (int.Parse(valuesSheet.Cells[rowNumber, i + 1].Value.ToString()) <= 10))
                        {
                            tempEsgIssue.BussinessImpact = int.Parse(valuesSheet.Cells[rowNumber, i].Value.ToString());
                        }
                        else
                        {
                            errors.Add(string.Format("Business Impact values have to be between 0 and 10. Row: {0}", (rowNumber + 1)));
                        }
                    else
                    {
                        errors.Add(string.Format("Business Impact cell on row: {0} is empty", (rowNumber + 1)));
                    }

                    if (process)
                    {
                        tempEsgIssue.SubPeerGroupId = int.Parse(valuesSheet.Cells[rowNumber, SubIndustryCodeCell].Value.ToString());
                        tempEsgIssue.KeyEsgIssueCode = int.Parse(valuesSheet.Cells[rowNumber, KeyEsgCodeCell].Value.ToString());

                        AppendToKeyEsgIssues(tempEsgIssue, ValuesSheet);
                    }
                    
                }
                rowNumber++;
            }
            return new Tuple<bool, List<string>>(!errors.Any(),errors);
        }

        private Tuple<bool,List<string>> ValidateEventIndicatorsSheet(IWorkbook book, bool process)
        {
            var errors = new List<string>();
            var eventIndicatorSheet = book.Worksheets[EventIndicatorsSheet];

            int rowNumber = StartingRowNumber;

            int numberOfEventIndicators = GetNumberOfEventIndicators(book);

            List<BaseEventIndicator> baseEventIndicator = _clientDwRepository.GetBaseEventIndicators().ToList();

            for (; ; )
            {
                if ((eventIndicatorSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) && (eventIndicatorSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                    break;

                if ((eventIndicatorSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null) || (eventIndicatorSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null))
                {
                    if (eventIndicatorSheet.Cells[rowNumber, SubIndustryCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Sub Industry Code on row: {0} cannot be empty", (rowNumber + 1)));
                    }

                    if (eventIndicatorSheet.Cells[rowNumber, KeyEsgCodeCell].Value == null)
                    {
                        errors.Add(string.Format("Key ESG Issue Code on row: {0} cannot be empty", (rowNumber + 1)));
                    }
                }
                else
                {
                    var tempEsgIssue = new KeyEsgIssueImpact();
                    tempEsgIssue.TemporaryIndicators = new List<KeyEsgIssueIndicator>();
                    var indicators = new List<KeyEsgIssueIndicator>();

                    for (int j = 0; j <= numberOfEventIndicators; j++)
                    {
                        if (eventIndicatorSheet.Cells[rowNumber, j + 5].Value != null)
                            if (eventIndicatorSheet.Cells[rowNumber, j + 5].Value.ToString() == "1")
                            {
                                KeyEsgIssueIndicator indicator = new KeyEsgIssueIndicator();
                                indicator.Name = baseEventIndicator.Where(bei => bei.Name == eventIndicatorSheet.Cells[EventIndicatorRow, j + 5].Value.ToString()).Select(bei => bei.Name).SingleOrDefault();
                                indicator.Number = baseEventIndicator.Where(bei => bei.Name == eventIndicatorSheet.Cells[EventIndicatorRow, j + 5].Value.ToString()).Select(bei => bei.Number).SingleOrDefault();

                                indicator.Type = KeyEsgIssueIndicatorType.EventIndicator;

                                if (indicator.Name != null)
                                    indicators.Add(indicator);

                            }
                    }

                    if (process)
                    {
                        tempEsgIssue.SubPeerGroupId = int.Parse(eventIndicatorSheet.Cells[rowNumber, SubIndustryCodeCell].Value.ToString());
                        tempEsgIssue.KeyEsgIssueCode = int.Parse(eventIndicatorSheet.Cells[rowNumber, KeyEsgCodeCell].Value.ToString());
                        tempEsgIssue.TemporaryIndicators = indicators;

                        AppendToKeyEsgIssues(tempEsgIssue, EventIndicatorsSheet);
                    }

                    rowNumber++;
                }
            }

            return new Tuple<bool, List<string>>(!errors.Any(),errors);
        }
        
        #endregion


        private void AppendToKeyEsgIssues(KeyEsgIssueImpact valueToAppend, int fromSheet)
        {

            

            foreach (KeyEsgIssueImpact issues in _keyEsgIssues)
            {
                
                if ((issues.KeyEsgIssueCode != valueToAppend.KeyEsgIssueCode) ||
                    (issues.SubPeerGroupId != valueToAppend.SubPeerGroupId)) continue;
                
                if (fromSheet == ValuesSheet)
                {
                    
                    issues.BussinessImpact = valueToAppend.BussinessImpact;
                    issues.SustainabilityImpact = valueToAppend.SustainabilityImpact;
                    Debug.WriteLine(string.Format("{0} {1} {2},{3}", issues.KeyEsgIssueCode, issues.SubPeerGroupId, issues.CompanyTypeTemplate, issues.Id));
                }

                if (fromSheet == EventIndicatorsSheet)
                {
                    issues.TemporaryIndicators.AddRange(valueToAppend.TemporaryIndicators);
                }
            }
        }

        #region Counters
        public int GetNumberOfRpIndicators(IWorkbook book, int tipIndicator)
        {
            var indicatorsSheet = book.Worksheets[tipIndicator];

            int numberOfRpIndicators = 0;

            for (int i = 5; ; )
            {
                if (indicatorsSheet.Cells[0, i].Value == null)
                    break;

                i++;
                numberOfRpIndicators++;
            }

            return numberOfRpIndicators;
        }

        public int GetNumberOfEventIndicators(IWorkbook book)
        {
            var indicatorsSheet = book.Worksheets[EventIndicatorsSheet];

            int numberOfEventIndicators = 0;

            for (int i = 5; ; )
            {
                if (indicatorsSheet.Cells[1, i].Value == null)
                    break;

                i++;
                numberOfEventIndicators++;
            }

            return numberOfEventIndicators;
        }
        #endregion

    }
}
